package com.example.score.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.score.entity.StudentSubmit;
import com.example.score.entity.StudentSubmitLevel;
import com.example.score.entity.input.SchoolBaseInput;
import com.example.score.entity.vo.StudentSubmitVo;
import org.apache.ibatis.annotations.*;

import java.time.LocalDateTime;
import java.util.List;
import java.util.Map;

@Mapper
public interface StudentSubmitMapper extends BaseMapper<StudentSubmit> {
    @Select("SELECT * FROM t_student_submit WHERE student_id = #{studentId} AND class_info_id = #{classId}")
    StudentSubmit selectByStudentIdAndClassId(@Param("studentId") String studentId, @Param("classId") Long classId);

    @Select("SELECT * FROM t_student_submit WHERE " +
            "student_id = #{studentId} " +
            "AND class_id = #{classId} " +
            "and exam_detail_id = #{examDetailId} " +
            "and submit_status = 1")
    StudentSubmit selectByStudentIdAndSubmit(@Param("studentId") String studentId, @Param("classId") Long classId,@Param("examDetailId") Long examDetailId);

    @Select("SELECT * FROM t_student_submit WHERE " +
            "class_id = #{classId} " +
            "and exam_detail_id = #{examDetailId} " +
            "and submit_status = 1 " +
            "order by score desc")
    List<StudentSubmit> selectByStudentSort(@Param("classId") Long classId, @Param("examDetailId") Long examDetailId);

    @Insert("<script>" +
            "INSERT INTO t_student_submit " +
            "(class_id,class_info_id, student_id,student_name, submit_status,student_num, score, created_time, updated_time,exam_detail_id,paper_images," +
            "raw_student_num, student_oms_id) VALUES " +
            "<foreach collection='list' item='item' separator=','>" +
            "(#{item.classId},#{item.classInfoId}, #{item.studentId},#{item.studentName}, #{item.submitStatus},#{item.studentNum}, #{item.score}" +
            ", now(), now(),#{item.examDetailId},#{item.paperImages}, #{item.rawStudentNum}, #{item.studentOmsId})" +
            "</foreach>" +
            "</script>")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    void insertBatch(@Param("list") List<StudentSubmit> submits);

    @Select("SELECT * FROM t_student_submit WHERE exam_detail_id = #{examDetailId} and submit_status = 1")
    List<StudentSubmit> selectByExamDetailId(@Param("examDetailId") Long examDetailId);

    @Select("SELECT * FROM t_student_submit WHERE exam_detail_id = #{examDetailId}")
    List<StudentSubmit> selectByExamDetailIdIgnoreStatus(@Param("examDetailId") Long examDetailId);

    @Select("SELECT ss.*, ci.class_name " +
            "FROM t_student_submit ss " +
            "LEFT JOIN t_class_info ci ON ss.class_info_id = ci.id " +
            "WHERE ss.exam_detail_id = #{examDetailId} " +
            "AND (#{classId} IS NULL OR ci.class_id = #{classId}) " +
            "ORDER BY ci.class_name, ss.student_num")
    List<StudentSubmit> selectByExamAndClass(@Param("examDetailId") Long examDetailId, @Param("classId") Long classId);

    @Select("SELECT ss.*, ci.class_name " +
            "FROM t_student_submit ss " +
            "LEFT JOIN t_class_info ci ON ss.class_info_id = ci.id " +
            "WHERE ss.exam_detail_id = #{examDetailId} " +
            "ORDER BY ci.class_name, ss.student_num")
    List<StudentSubmit> selectsdByExamDetailId(@Param("examDetailId") Long examDetailId);

    @Select("SELECT * " +
            "FROM t_student_submit " +
            "WHERE exam_detail_id = #{examDetailId} ")
    List<StudentSubmit> selectListByExamDetailId(@Param("examDetailId") Long examDetailId);

    @Select("SELECT * FROM t_student_submit WHERE exam_detail_id = #{examDetailId}  and class_info_id = #{classInfoId}")
    List<StudentSubmit> selectByClassInfoId(@Param("examDetailId") Long examDetailId,@Param("classInfoId") Long classInfoId);

    @Select("SELECT COUNT(*) FROM t_student_submit " +
            "WHERE exam_detail_id = #{examDetailId} AND submit_status = 1")
    int countValidSubmitsByExamId(@Param("examDetailId") Long examDetailId);

    @Select("SELECT COUNT(*) FROM t_student_submit " +
            "WHERE class_info_id = #{classInfoId} AND submit_status = 1")
    int countValidSubmitsByClassId(@Param("classInfoId") Long classInfoId);

    @Select("SELECT student_name FROM t_student_submit " +
            "WHERE class_id = #{classInfoId} AND exam_detail_id =#{detailId} AND submit_status != 1")
    List<String> findUnsubmittedStudents(@Param("classInfoId") Long classInfoId, @Param("detailId") Long id);

    @Select("<script>" +
            "SELECT s.*, c.class_name " +
            "FROM t_student_submit s " +
            "LEFT JOIN t_class_info c ON s.class_info_id = c.id " +
            "WHERE s.exam_detail_id IN " +
            "<foreach collection='examDetailIds' item='examId' open='(' separator=',' close=')'>" +
            "#{examId}" +
            "</foreach> " +
            "AND c.class_id = #{classId} " +
            "ORDER BY s.exam_detail_id DESC, s.score DESC" +
            "</script>")
    List<StudentSubmit> selectByExamDetailIds(
            @Param("examDetailIds") List<Long> examDetailIds,
            @Param("classId") Long classId
    );

    @Select("<script>" +
            "SELECT *" +
            "FROM t_student_submit  " +
            "WHERE exam_detail_id IN " +
            "<foreach collection='examDetailIds' item='examId' open='(' separator=',' close=')'>" +
            "#{examId}" +
            "</foreach> " +
            "AND class_id = #{classId} " +
            "ORDER BY exam_detail_id DESC, score DESC" +
            "</script>")
    List<StudentSubmit> selectListByExamDetailIdsAndClassId(
            @Param("examDetailIds") List<Long> examDetailIds,
            @Param("classId") Long classId
    );

    /**
     * 根据准考证号和姓名查询学生信息
     */
    @Select("SELECT DISTINCT student_id as studentId, student_num as studentNum, " +
            "student_name as studentName, class_id as classId " +
            "FROM t_student_submit " +
            "WHERE student_num = #{studentNum} AND student_name = #{studentName} " +
            "LIMIT 1")
    Map<String, Object> findStudent(@Param("studentNum") String studentNum,
                                  @Param("studentName") String studentName);

    /**
     * 根据准考证号查询作业列表
     */
    @Select("SELECT DISTINCT ss.exam_detail_id as examDetailId, " +
            "ss.created_time as submitTime, " +
            "eb.exam_id as examId," +
            "ss.score as score, " +
            "ss.submit_status as status, " +
            "eb.exam_name as examName " +
            "FROM t_student_submit ss " +
            "LEFT JOIN t_exam_detail ed ON ss.exam_detail_id = ed.id " +
            "LEFT JOIN t_exam_base eb on ed.exam_id = eb.exam_id " +
            "WHERE ss.student_num = #{studentNum} " +
            "ORDER BY ss.created_time DESC")
    List<Map<String, Object>> findHomeworkList(@Param("studentNum") String studentNum);

    /**
     * 根据考试ID和学生ID查询学生提交记录
     */
    @Select("SELECT * FROM t_student_submit WHERE exam_detail_id = #{examId} AND student_id = #{studentId} LIMIT 1")
    StudentSubmit selectByExamAndStudent(@Param("examId") Long examId, @Param("studentId") String studentId);

    /**
     * 根据学生ID和日期范围查询学生提交记录
     */
    @Select("SELECT * FROM t_student_submit WHERE student_id = #{studentId} " +
            "AND created_time BETWEEN #{startDate} AND #{endDate} ORDER BY created_time DESC")
    List<StudentSubmit> selectByStudentIdAndDateRange(
            @Param("studentId") String studentId,
            @Param("startDate") LocalDateTime startDate,
            @Param("endDate") LocalDateTime endDate);

    @Select("<script>" +
            "SELECT *" +
            "FROM t_student_submit  " +
            "WHERE id IN " +
            "<foreach collection='studentSubmitIds' item='submitId' open='(' separator=',' close=')'>" +
            "#{submitId}" +
            "</foreach> " +
            "</script>")
    List<StudentSubmit> selectByStudentSubmitIds(List<Long> studentSubmitIds);

    @Select("<script>" +
            "SELECT *" +
            "FROM t_student_submit  " +
            "WHERE exam_detail_id =#{examDetailId} AND submit_status = 1 AND class_id IN " +
            "<foreach collection='classIds' item='classId' open='(' separator=',' close=')'>" +
            "#{classId}" +
            "</foreach> " +
            "</script>")
    List<StudentSubmit> selectByExamDetailIdAndClassIds(@Param("examDetailId") Long id, @Param("classIds") List<Long> classIds);

    @Select("<script>" +
            "SELECT *" +
            "FROM t_student_submit  " +
            "WHERE submit_status = 1 and exam_detail_id IN " +
            "<foreach collection='examDetailIds' item='examId' open='(' separator=',' close=')'>" +
            "#{examId}" +
            "</foreach> " +
            " AND class_id IN " +
            "<foreach collection='classIds' item='classId' open='(' separator=',' close=')'>" +
            "#{classId}" +
            "</foreach> " +
            "</script>")
    List<StudentSubmit> selectByExamDetailIdsAndClassIds(@Param("examDetailIds") List<Long> examDetails, @Param("classIds") List<Long> classIds);

    @Select("select * from t_student_submit where student_num = #{studentNum}")
    List<StudentSubmit> selectExamDetailIdByStudentNum(String studentNum);



    /**
     * 校本资源统计扫描量
     * @param param 参数
     * @return 总数
     */
    int studentSubmitCount(@Param("param") SchoolBaseInput param);

    /**
     * 校本资源扫描量
     * @param param 查询参数
     * @return 学生提交列表
     */
    List<StudentSubmitVo> studentSubmitList(@Param("param") SchoolBaseInput param);

    /**
     * 根据 examDetailIds 查询 数据
     * @param list 作业详情id
     * @return 列表
     */
    List<StudentSubmitVo> listByExamDetailIds(@Param("list") List<Long> list);
}
